package org.nebuloop.photo.organizer.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.configuration.Configuration;
import org.apache.commons.lang.StringUtils;
import org.nebuloop.photo.organizer.data.ImageInfo;
import org.nebuloop.photo.organizer.data.ImageSource;

public final class ImageDAOImpl implements ImageDAO {

  private static final Logger LOGGER = Logger.getLogger(ImageDAOImpl.class.getName());
  private final String JDBC_DRIVER;
  private final String JDBC_URL;
  private final String JDBC_USER;
  private final String JDBC_PW;

  private Connection connection;
  private final String destPath;
  private static volatile ImageDAOImpl instance;

  public static ImageDAOImpl getInstance(final String destPath, final Configuration config) {
    if (instance == null) {
      synchronized (LOGGER) {
        if (instance == null) {
          instance = new ImageDAOImpl(destPath, config);
        }
      }
    }
    return instance;
  }

  private ImageDAOImpl(final String destPath, final Configuration config) {
    this.destPath = destPath;
    JDBC_DRIVER = config.getString("db.jdbc.driver");
    JDBC_URL = config.getString("db.jdbc.url");
    JDBC_USER = config.getString("db.jdbc.username");
    JDBC_PW = config.getString("db.jdbc.password");
  }

  @Override
  public void init() throws ClassNotFoundException, SQLException {
    Class.forName(JDBC_DRIVER);
    String jdbc_url_full = String.format(JDBC_URL, destPath + "imagedb");
    LOGGER.log(Level.INFO, "Connecting to database : {0}", jdbc_url_full);
    connection = DriverManager.getConnection(jdbc_url_full, JDBC_USER, JDBC_PW);
    DBSetup.init(JDBC_DRIVER);
    DBSetup.setupDB(connection);
    getDbImageCount();
  }

  @Override
  public void saveImageInfo(ImageInfo imageInfo) throws SQLException {
    if (imageInfo.getImageImportTime() == null) {
      imageInfo.setImageImportTime(new Date());
    }
    PreparedStatement pstmt = null;
    try {
      pstmt = connection
          .prepareStatement("insert into IMAGE_INFO (NAME, DEST_PATH, MD5, CREATE_TIME, IMPORTED_TIME, HAS_EXIF, HAS_GEOCODES) VALUES (?,?,?,?,?,?,?)");
      pstmt.clearParameters();
      pstmt.setString(1, imageInfo.getName());
      pstmt.setString(2, imageInfo.getDestPath());
      pstmt.setString(3, imageInfo.getMd5());
      pstmt.setTimestamp(4, new Timestamp(imageInfo.getImageCreationTime().getTime()));
      pstmt.setTimestamp(5, new Timestamp(imageInfo.getImageImportTime().getTime()));
      pstmt.setBoolean(6, imageInfo.isHasExif());
      pstmt.setBoolean(7, imageInfo.isHasGeocodes());
      pstmt.executeUpdate();
    } finally {
      try {
        if (pstmt != null) {
          pstmt.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  @Override
  public void saveImageSourceInfo(ImageSource imageSrc) throws SQLException {
    if (StringUtils.isBlank(imageSrc.getSource_id())) {
      throw new RuntimeException("Image SourceId is null");
    }
    if (imageSrc.getCheckTime() == null) {
      imageSrc.setCheckTime(new Date());
    }
    PreparedStatement pstmt = null;
    try {
      pstmt = connection.prepareStatement("select count(*) from SRC_INFO where IMG_ID=? and SRC_PATH=? and SRC_ID=?");
      pstmt.clearParameters();
      pstmt.setLong(1, imageSrc.getImgId());
      pstmt.setString(2, imageSrc.getSourcePath());
      pstmt.setString(3, imageSrc.getSource_id());
      try (ResultSet rs = pstmt.executeQuery()) {
        rs.next();
        if (rs.getLong(1) > 0) {
          LOGGER.log(Level.WARNING, "Image Source {0} is already present", imageSrc.getSourcePath());
          return;
        }
      }
      pstmt.close();

      pstmt = connection
          .prepareStatement("insert into SRC_INFO (IMG_ID, SRC_PATH, SRC_ID, CHECKED_TIME) VALUES (?,?,?,?)");
      pstmt.clearParameters();
      pstmt.setLong(1, imageSrc.getImgId());
      pstmt.setString(2, imageSrc.getSourcePath());
      pstmt.setString(3, imageSrc.getSource_id());
      pstmt.setTimestamp(4, new Timestamp(imageSrc.getCheckTime().getTime()));
      pstmt.executeUpdate();
      LOGGER.log(Level.INFO, "Saved source information {0}", imageSrc);
    } finally {
      try {
        if (pstmt != null) {
          pstmt.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  @Override
  public ImageInfo getImageByMD5(String md5) throws SQLException {
    ImageInfo imageOrgInfo;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = connection.prepareStatement("select * from IMAGE_INFO where MD5=?");
      pstmt.clearParameters();
      pstmt.setString(1, md5);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        imageOrgInfo = fromResultSet(rs);
      } else {
        imageOrgInfo = null;
      }
      return imageOrgInfo;
    } finally {
      try {
        if (pstmt != null) {
          pstmt.close();
        }
        if (rs != null) {
          rs.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  @Override
  public List<ImageSource> getImageSources(long image_id) throws SQLException {
    List<ImageSource> list = new ArrayList<>();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = connection.prepareStatement("select * from SRC_INFO where IMG_ID=?");
      pstmt.clearParameters();
      pstmt.setLong(1, image_id);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        ImageSource imageSrc = imageSourceFromResultSet(rs);
        list.add(imageSrc);
      }
      return list;
    } finally {
      try {
        if (pstmt != null) {
          pstmt.close();
        }
        if (rs != null) {
          rs.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  private ImageInfo fromResultSet(ResultSet rs) throws SQLException {
    ImageInfo imageOrgInfo = new ImageInfo();
    imageOrgInfo.setId(rs.getLong("ID"));
    imageOrgInfo.setName(rs.getString("NAME"));
    imageOrgInfo.setDestPath(rs.getString("DEST_PATH"));
    imageOrgInfo.setMd5(rs.getString("MD5"));
    imageOrgInfo.setImageCreationTime(rs.getTimestamp("CREATE_TIME"));
    imageOrgInfo.setImageImportTime(rs.getTimestamp("IMPORTED_TIME"));
    imageOrgInfo.setHasExif(rs.getBoolean("HAS_EXIF"));
    imageOrgInfo.setHasGeocodes(rs.getBoolean("HAS_GEOCODES"));
    return imageOrgInfo;
  }

  private ImageSource imageSourceFromResultSet(ResultSet rs) throws SQLException {
    ImageSource imgSource = new ImageSource();
    imgSource.setId(rs.getLong("ID"));
    imgSource.setImgId(rs.getLong("ING_ID"));
    imgSource.setSourcePath(rs.getString("SRC_PATH"));
    imgSource.setSource_id(rs.getString("SRC_ID"));
    imgSource.setCheckTime(rs.getTimestamp("CHECKED_TIME"));
    return imgSource;
  }

  @Override
  public void close() {
    try {
      connection.close();
    } catch (SQLException ex) {
      LOGGER.log(Level.WARNING, "Error in closing db connection", ex);
    }
  }

  @Override
  public int getDbImageCount() throws SQLException {
    ResultSet rs = null;
    try (Statement stmt = connection.createStatement()) {
      rs = stmt.executeQuery("select count(id) from IMAGE_INFO ");
      rs.next();
      int imageCount = rs.getInt(1);
      rs.close();
      return imageCount;
    }
  }

}